﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace JamesThew.Dashboard
{
    public class PostDB
    {

        public List<Post> Get(String[] Args)
        {
            List<Post> lp = new List<Post>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("AdvancedGetPostsWithCat", con);
            cmd.CommandType = CommandType.StoredProcedure;

            if (Args[0] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostID", JamesThew.Includes.Core.ParseInt(Args[0])));
            }
            if (Args[1] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostAuthor", JamesThew.Includes.Core.ParseInt(Args[1])));
            }
            if (Args[2] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostTitle", Args[2]));
            }
            if (Args[3] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostContent", Args[3]));
            }
            if (Args[4] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostStatus", Args[4]));
            }
            if (Args[5] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostDateMin", Args[5]));
            }
            if (Args[6] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostDateMax", Args[6]));

            }
            if (Args[7] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostModifiedMin", Args[7]));
            }
            if (Args[8] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostModifiedMax", Args[8]));
            }
            if (Args[9] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostType", Args[9]));
            }
            if (Args[10] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@PostCommentCount", JamesThew.Includes.Core.ParseInt(Args[10])));
            }

            if (Args[11] != null)
            {
                cmd.Parameters.Add(new SqlParameter("@CategoryID", JamesThew.Includes.Core.ParseInt(Args[11])));
            }
            

            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int PostID = (int)dt.Rows[i][0];
                int PostAuthor = (int) dt.Rows[i][1];
                String PostDate = dt.Rows[i][2].ToString();
                String PostTitle = dt.Rows[i][3].ToString();
                String PostContent = dt.Rows[i][4].ToString();
                String PostExcerpt = dt.Rows[i][5].ToString();
                String PostStatus = dt.Rows[i][6].ToString();
                String PostCommentStatus = dt.Rows[i][7].ToString();
                String PostPassword = dt.Rows[i][8].ToString();
                String PostModified = dt.Rows[i][9].ToString();
                int PostParent = (int) dt.Rows[i][10];
                String PostGUID = dt.Rows[i][11].ToString();
                int PostMenuOrder = (int) dt.Rows[i][12];
                String PostType = dt.Rows[i][13].ToString();
                String PostMimeType = dt.Rows[i][14].ToString();
                int PostCommentCount = (int)dt.Rows[i][15];
                String PostIcon = dt.Rows[i][16].ToString();
                int PostViews = (int) dt.Rows[i][17];
                String PostThumbnail = dt.Rows[i][18].ToString();

                Post post = new Post(PostID, PostAuthor, PostDate, PostTitle, PostContent,
            PostExcerpt, PostStatus, PostCommentStatus, PostPassword,
            PostModified, PostParent, PostGUID, PostMenuOrder, PostType,
            PostMimeType, PostCommentCount, PostIcon, PostViews, PostThumbnail);

                lp.Add(post);
            }

            con.Close();

            return lp;
        }

        public Boolean Delete(int PostID)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("DeletePost", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@PostID", PostID));

            Int32 check = cmd.ExecuteNonQuery();
            con.Close();
            if (check > 0) return true;
            return false;
        }

        public Boolean Add(Post post)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("InsertPost", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@PostAuthor", post.PostAuthor));
            cmd.Parameters.Add(new SqlParameter("@PostDate", post.PostDate));
            cmd.Parameters.Add(new SqlParameter("@PostTitle", post.PostTitle));
            cmd.Parameters.Add(new SqlParameter("@PostContent", post.PostContent));
            cmd.Parameters.Add(new SqlParameter("@PostExcerpt", post.PostExcerpt));
            cmd.Parameters.Add(new SqlParameter("@PostStatus", post.PostStatus));
            cmd.Parameters.Add(new SqlParameter("@PostCommentStatus", "open"));
            cmd.Parameters.Add(new SqlParameter("@PostPassword", post.PostPassword));
            cmd.Parameters.Add(new SqlParameter("@PostModified", post.PostModified));
            cmd.Parameters.Add(new SqlParameter("@PostParent", post.PostParent));
            cmd.Parameters.Add(new SqlParameter("@PostGUID", post.PostGUID));
            cmd.Parameters.Add(new SqlParameter("@PostMenuOrder", post.PostMenuOrder));
            cmd.Parameters.Add(new SqlParameter("@PostType", post.PostType));
            cmd.Parameters.Add(new SqlParameter("@PostMimeType", post.PostMimeType));
            cmd.Parameters.Add(new SqlParameter("@PostCommentCount", post.PostCommentCount));
            cmd.Parameters.Add(new SqlParameter("@PostIcon", post.PostIcon));
            cmd.Parameters.Add(new SqlParameter("@PostViews", post.PostViews));
            cmd.Parameters.Add(new SqlParameter("@PostThumbnail", post.PostThumbnail));

            cmd.Parameters.Add(new SqlParameter("@PostID", SqlDbType.Int, 6));
            cmd.Parameters["@PostID"].Direction = ParameterDirection.Output;

            Int32 check = cmd.ExecuteNonQuery();
            string khoa = cmd.Parameters["@PostID"].Value.ToString();
            Int32 PostID = JamesThew.Includes.Core.ParseInt(khoa);

            post.PostID = PostID;

            con.Close();
            if (check > 0) return true;
            return false;

        }


        public bool Update(Post post)
        {
           
            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("UpdatePost", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@PostID", post.PostID));
            cmd.Parameters.Add(new SqlParameter("@PostAuthor", post.PostAuthor));
            cmd.Parameters.Add(new SqlParameter("@PostDate", post.PostDate));
            cmd.Parameters.Add(new SqlParameter("@PostTitle", post.PostTitle));
            cmd.Parameters.Add(new SqlParameter("@PostContent", post.PostContent));
            cmd.Parameters.Add(new SqlParameter("@PostExcerpt", post.PostExcerpt));
            cmd.Parameters.Add(new SqlParameter("@PostStatus", post.PostStatus));
            cmd.Parameters.Add(new SqlParameter("@PostCommentStatus", "open"));
            cmd.Parameters.Add(new SqlParameter("@PostPassword", post.PostPassword));
            cmd.Parameters.Add(new SqlParameter("@PostModified", post.PostModified));
            cmd.Parameters.Add(new SqlParameter("@PostParent", post.PostParent));
            cmd.Parameters.Add(new SqlParameter("@PostGUID", post.PostGUID));
            cmd.Parameters.Add(new SqlParameter("@PostMenuOrder", post.PostMenuOrder));
            cmd.Parameters.Add(new SqlParameter("@PostType", post.PostType));
            cmd.Parameters.Add(new SqlParameter("@PostMimeType", post.PostMimeType));
            cmd.Parameters.Add(new SqlParameter("@PostCommentCount", post.PostCommentCount));
            cmd.Parameters.Add(new SqlParameter("@PostIcon", post.PostIcon));
            cmd.Parameters.Add(new SqlParameter("@PostViews", post.PostViews));
            cmd.Parameters.Add(new SqlParameter("@PostThumbnail", post.PostThumbnail));

            Int32 check = cmd.ExecuteNonQuery();
            con.Close();
            if (check > 0) return true;
            return false;

        }

    }
}